﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Diagnostics;
using System.Text;
using System.Collections;
using System.Collections.Specialized;
using Microsoft.Office.Interop.Excel;
using Microsoft.Win32;

namespace VitCode.Web.UI.ExcelFileGenerator
{
    /// <summary>
    /// Excel文件生成器。根据指定的数据源，生成一个Excel文件。
    /// </summary>
    public class ExcelFileGenerator
    {
        private int _MaxRowCount=50000;
        private object oMissing=System.Reflection.Missing.Value;
        private string m_title;
        private NameValueCollection m_Headers;
        private NameValueCollection m_Footers;
        private int m_HeaderFieldCount;
        private int m_FooterFieldCount;
        private string m_datafilepath;

        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelFileGenerator(string datafilepath)
        {
            m_Headers=new NameValueCollection();
            m_Footers=new NameValueCollection();
            m_HeaderFieldCount=0;
            m_FooterFieldCount=0;
            m_datafilepath=datafilepath;
        }


        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelFileGenerator()
        {
            m_Headers=new NameValueCollection();
            m_Footers=new NameValueCollection();
            m_HeaderFieldCount=0;
            m_FooterFieldCount=0;
            m_datafilepath=System.Environment.GetFolderPath(System.Environment.SpecialFolder.InternetCache);
        }

        private bool IsExistsColumn(System.Data.DataTable table,string columnName,ArrayList FieldTable)
        {
            for(int c=0;c<FieldTable.Count;c++)
            {
                string colName=((Hashtable)FieldTable[c])["Field"].ToString();
                if(columnName.ToLower()==colName.ToLower())
                {
                    table.Columns[colName].ColumnName=((Hashtable)FieldTable[c])["ColumnName"].ToString();
                    return true;
                }
            }
            return false;
        }

        #region 不需要的代码
        private void ExportDataWithOLEDB()
        {
            //使用OleDb填充数据
            //			string excelConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="
            //				+ FilePath + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;""";
            //			System.Data.OleDb.OleDbConnection excelConn = new System.Data.OleDb.OleDbConnection(excelConnectString);
            //			excelConn.Open();
            //			OleDbCommand excelCmd=new OleDbCommand();
            //			excelCmd.Connection=excelConn;
            //			int startRecordIndex=0;
            //			try
            //			{
            //				//循环所有工作表,并填充数据
            //				for(int i=0;i<sheetCount;i++)
            //				{
            //					startRecordIndex=this._MaxRowCount*i;
            //					this.GenerateSheet(excelCmd,dataTable,startRecordIndex,"Sheet"+Convert.ToString(i+1)+"$");
            //				}
            //			}
            //			catch
            //			{
            //				throw;
            //			}
            //			finally
            //			{
            //				excelConn.Close();
            //			}
        }
        /// <summary>
        /// 使用OLEDB方式将数据导出到某个工作表
        /// </summary>
        /// <param name="excelCmd"></param>
        /// <param name="dataTable"></param>
        /// <param name="startRecordIndex"></param>
        /// <param name="sheetName"></param>
        private void GenerateSheet(OleDbCommand excelCmd, System.Data.DataTable dataTable, int startRecordIndex, string sheetName)
        {
            //			//填充数据
            //			if(this._InsertCommandString.Length==0)
            //				this._InsertCommandString=this.GenerateComandString(dataTable,sheetName,excelCmd);
            //			excelCmd.CommandText=this._InsertCommandString;
            //			int rowcount=0;
            //			if(dataTable.Rows.Count-startRecordIndex>this._MaxRowCount)
            //				rowcount=this._MaxRowCount;
            //			else
            //				rowcount=dataTable.Rows.Count-startRecordIndex;
            //			for (int j = startRecordIndex; j < rowcount; j++)
            //			{
            //				for (int i = 0; i < dataTable.Columns.Count; i++)
            //				{
            //					excelCmd.Parameters["@"+dataTable.Columns[i].ColumnName].Value= dataTable.Rows[j][i].ToString();
            //				}
            //				excelCmd.ExecuteNonQuery();
            //			}
        }
        #endregion


        /// <summary>
        /// 显示在文件内的标题
        /// </summary>
        public string Title
        {
            get
            {
                return this.m_title;
            }
            set
            {
                this.m_title=value;
            }
        }

        /// <summary>
        /// 显示在数据前的文件头信息
        /// </summary>
        public NameValueCollection Header
        {
            get{return m_Headers;}
            set{m_Headers=value;}
        }

        /// <summary>
        /// 显示在数据后的页脚信息
        /// </summary>
        public NameValueCollection Footer
        {
            get{return m_Footers;}
            set{m_Footers=value;}
        }

        private int HeaderFieldCount
        {
            set{m_HeaderFieldCount=value;}
        }
        private int FooterFiledCount
        {
            set{m_FooterFieldCount=value;}
        }

        /// <summary>
        /// 生成文件头
        /// </summary>
        /// <param name="xlApp"></param>
        /// <param name="dataSheet"></param>
        /// <param name="ColumnCount"></param>
        /// <returns></returns>
        private int GenerateHeader(ApplicationClass xlApp,Worksheet dataSheet,int ColumnCount)
        {
            //生成标题及文件头
            int titleRowCount=0;
            if(Title!=null && Title.Trim().Length>0)
            {
                titleRowCount=1;
                int endCell=65+ColumnCount-1;
                string endCellName=Convert.ToChar(endCell)+"1";
                Range titleRange=dataSheet.get_Range("A1",endCellName);
                titleRange.Font.Bold=true;
                titleRange.Select();
                ((Range)xlApp.Selection).MergeCells = true;
                ((Range)xlApp.Selection).HorizontalAlignment=XlHAlign.xlHAlignCenter;
                dataSheet.Cells[1,1]=Title;
                titleRange=null;
            }
            //文件头
            int temp=0;
            if(Header.Count>0)
            {
                if(m_HeaderFieldCount==0)
                    m_HeaderFieldCount=2;
                if(Header.Count % 2 ==0)
                    temp=Header.Count/2;
                else
                    temp=Header.Count/2+1;
                for(int i=0;i<temp;i++)
                {
                    if(i*2<Header.Count)
                    {
                        dataSheet.Cells[titleRowCount+1+i,1]=Header.Keys[i*2]+"：";
                        dataSheet.Cells[titleRowCount+1+i,2]=Header[i*2];
                    }
                    if(i*2+1<Header.Count)
                    {
                        dataSheet.Cells[titleRowCount + 1 + i, ColumnCount - 1] = Header.Keys[i * 2 + 1] + "：";
                        dataSheet.Cells[titleRowCount+1+i,ColumnCount]=Header[i*2+1];
                    }
                }
            }

            if(titleRowCount+temp>0)
                return titleRowCount+temp+2;
            else
                return 1;
        }

        /// <summary>
        /// 生成文件页脚
        /// </summary>
        /// <param name="dataSheet"></param>
        /// <param name="rowIndex"></param>
        /// <param name="ColumnCount"></param>
        private void GenerateFooter(Worksheet dataSheet,int rowIndex,int ColumnCount)
        {
            int temp=0;
            rowIndex++;
            if(Footer.Count>0)
            {
                if(m_FooterFieldCount==0)
                    m_FooterFieldCount=2;
                if(Footer.Count % 2 ==0)
                    temp=Footer.Count/2;
                else
                    temp=Footer.Count/2+1;
                for(int i=0;i<temp;i++)
                {
                    if(i*2<Footer.Count)
                    {
                        dataSheet.Cells[rowIndex + i, 1] = Footer.Keys[i * 2] + "：";
                        dataSheet.Cells[rowIndex+i,2]=Footer[i*2];
                    }
                    if(i*2+1<Footer.Count)
                    {
                        dataSheet.Cells[rowIndex + i, ColumnCount - 1] = Footer.Keys[i * 2 + 1] + "：";
                        dataSheet.Cells[rowIndex+i,ColumnCount]=Footer[i*2+1];
                    }
                }
            }
        }
        /// <summary>
        /// 使用数组方式将数据导出到某个工作表
        /// </summary>
        /// <param name="dataSheet"></param>
        /// <param name="dataTable"></param>
        /// <param name="startRecordIndex"></param>
        /// <param name="xlApp"></param>
        /// <param name="FieldTable">用作表头的字段列表，列表的每项是一个哈希表实例，这个哈希表包含有"Field"和"ColumnName"两个键。</param>
        private void GenerateSheet(ApplicationClass xlApp,Worksheet dataSheet,System.Data.DataTable dataTable,int startRecordIndex,ArrayList FieldTable)
        {
            //生成列头
            int colIndex=0;
            string[] needExportColumns;
            if(FieldTable!=null)
                needExportColumns=new string[FieldTable.Count];
            else
                needExportColumns=new string[dataTable.Columns.Count];
            int startRowIndex=1;
            startRowIndex=this.GenerateHeader(xlApp,dataSheet,needExportColumns.Length);
            for(int j=0;j<dataTable.Columns.Count;j++)
            {
                if(FieldTable!=null)
                {
                    if(this.IsExistsColumn(dataTable,dataTable.Columns[j].ColumnName,FieldTable))
                    {
                        dataSheet.Cells[startRowIndex,colIndex+1]=dataTable.Columns[j].ColumnName;
                        needExportColumns[colIndex]=dataTable.Columns[j].ColumnName;
                        colIndex++;
                    }
                }
                else
                {
                    dataSheet.Cells[startRowIndex,j+1]=dataTable.Columns[j].ColumnName;
                    needExportColumns[j]=dataTable.Columns[j].ColumnName;
                }
            }
            Range headRange=dataSheet.get_Range("A"+startRowIndex.ToString(),oMissing);
            headRange=headRange.get_Resize(startRowIndex,dataTable.Columns.Count);
            headRange.Font.Bold=true;
            headRange.Borders.LineStyle=1;
            headRange.Borders[XlBordersIndex.xlEdgeLeft].Weight=XlBorderWeight.xlThick;
            headRange.Borders[XlBordersIndex.xlEdgeTop].Weight=XlBorderWeight.xlThick;
            headRange.Borders[XlBordersIndex.xlEdgeRight].Weight=XlBorderWeight.xlThick;
            int rowcount=0;
            if(dataTable.Rows.Count-startRecordIndex>this._MaxRowCount)
                rowcount=this._MaxRowCount;
            else
                rowcount=dataTable.Rows.Count-startRecordIndex;
            int colcount=needExportColumns.Length;			
            object [,] dataAry=new object[rowcount,colcount];
            for(int r=0;r<rowcount;r++)
            {
                for(int c=0;c<colcount;c++)
                {
                    dataAry[r,c]=dataTable.Rows[r][needExportColumns[c]].ToString();
                }
            }
            Range valueRange = dataSheet.get_Range("A"+Convert.ToString(startRowIndex+1),oMissing );
            valueRange = valueRange.get_Resize(rowcount,colcount);
            valueRange.Font.Bold=false;
            valueRange.NumberFormat="@";
            valueRange.Value2=dataAry;
            valueRange.Borders.LineStyle=1;
//			valueRange.Borders[XlBordersIndex.xlEdgeLeft].Weight=XlBorderWeight.xlThick;
//			valueRange.Borders[XlBordersIndex.xlEdgeBottom].Weight=XlBorderWeight.xlThick;
//			valueRange.Borders[XlBordersIndex.xlEdgeRight].Weight=XlBorderWeight.xlThick;


            //生成页脚
            this.GenerateFooter(dataSheet,rowcount+startRowIndex+1,needExportColumns.Length);
            headRange=null;
        }

        /// <summary>
        /// 生成Excel文件。
        /// </summary>
        /// <param name="dataTable">将要填充到Excel文件的数据源</param>
        /// <param name="FieldTable">用作表头的字段列表，列表的每项是一个哈希表实例，这个哈希表包含有"Field"和"ColumnName"两个键。此参数为空，表示导出所有字段</param>
        /// <returns>生成的Excel文件路径</returns>
        private string GenerateExcelFile(System.Data.DataTable dataTable,ArrayList FieldTable)
        {

            if(dataTable==null)
                throw new ArgumentNullException("dataTable","Ҫ����������ԴΪ��");
            string tempFileName;
            try
            {
                tempFileName=Path.GetTempFileName();
                FileInfo fi=new FileInfo(tempFileName);
                tempFileName=fi.Name.Replace(fi.Extension,".xls");
            }
            catch
            {
                DateTime dt=DateTime.Now;
                tempFileName=dt.Year.ToString()+dt.Month.ToString()+dt.Day.ToString()+dt.Hour.ToString()+dt.Minute.ToString()+dt.Second.ToString()+".xls";
            }

            //预计需要的工作表数
            int sheetCount=0;
            string FilePath=m_datafilepath+"\\"+tempFileName;

            ApplicationClass xlApp = new ApplicationClass();
            Workbook workbook=null;
            try
            {
                workbook=xlApp.Workbooks.Add(oMissing);
                if(dataTable.Rows.Count % this._MaxRowCount==0)
                    sheetCount=dataTable.Rows.Count/this._MaxRowCount;
                else
                    sheetCount=dataTable.Rows.Count/this._MaxRowCount+1;
                //循环向工作表中加入数据,如果三个默认工作表已用完,需要增加工作表
                int startRecordIndex=0;
                for(int i=0;i<sheetCount;i++)
                {
                    Worksheet xlWorksheet;
                    if(i>=3)
                    {
                        Worksheet lastsheet=(Worksheet)xlApp.Worksheets[i];
                        xlWorksheet=(Worksheet)xlApp.Worksheets.Add(oMissing,lastsheet,oMissing,oMissing);
                        xlWorksheet.Name="Sheet"+Convert.ToString(1+i);
                    }
                    else
                    {
                        xlWorksheet=(Worksheet)xlApp.Worksheets[i+1];
                    }
                    startRecordIndex=this._MaxRowCount*i;
                    this.GenerateSheet(xlApp,xlWorksheet,dataTable,startRecordIndex,FieldTable);
                }
                //workbook.SaveAs(FilePath, oMissing, oMissing, oMissing, oMissing, oMissing, XlSaveAsAccessMode.xlExclusive, oMissing, oMissing, oMissing, oMissing, oMissing);
               bool isResult= SaveExcel(FilePath, workbook,oMissing);//Excel2007
                if(!isResult)
                    throw new ArgumentNullException("Excel", "生成Execl2003兼容文件有误");
            }
            catch
            {
                throw;
            }
            finally
            {
                if(workbook!=null)
                    workbook.Close(oMissing,oMissing,oMissing);
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                workbook = null;
                xlApp=null;
                GC.Collect();
            }
            return FilePath;
        }

        public bool SaveExcel(string FilePath, Workbook wb, object oMissing)
        {
            bool isResult = false;
            int ifused = ExistsRegedit();
            switch (ifused)
            {
                case 2:
                    wb.SaveAs(FilePath, XlFileFormat.xlExcel8, Type.Missing,
                          Type.Missing, Type.Missing, Type.Missing,
                          XlSaveAsAccessMode.xlNoChange, Type.Missing,
                          Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    isResult = true;
                    break;
                case 1:
                    wb.SaveAs(FilePath, oMissing, oMissing, oMissing, oMissing, oMissing, XlSaveAsAccessMode.xlExclusive, oMissing,
                               oMissing, oMissing, oMissing, oMissing);
                    isResult = true;
                    break;
                default:
                    wb.SaveAs(FilePath, XlFileFormat.xlExcel8, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing,
                        XlSaveAsAccessMode.xlNoChange, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    isResult = true;
                    break;
            } 
            return isResult;

        }

        public Int32 ExistsRegedit()
        {
            int ifused = 0;
            RegistryKey rk = Registry.LocalMachine;
            RegistryKey akey = rk.OpenSubKey(@"SOFTWARE\\Microsoft\\Office\\11.0\\Word\\InstallRoot\\");
            RegistryKey akeytwo = rk.OpenSubKey(@"SOFTWARE\\Microsoft\\Office\\12.0\\Word\\InstallRoot\\");
            //Office2003
            if (akey != null)
            {
                string file03 = akey.GetValue("Path").ToString();
                if (File.Exists(file03 + "Excel.exe"))
                {
                    ifused = 1;
                }
            }
            //Office2007
            if (akeytwo != null)
            {
                string file07 = akeytwo.GetValue("Path").ToString();
                if (File.Exists(file07 + "Excel.exe"))
                {
                    ifused = 2;
                }
            }
            return ifused;
        }

        /// <summary>
        /// 将数据导出到一个Excel文件中，并返回这个Excel文件的路径
        /// </summary>
        /// <param name="dataTable">将要填充到Excel文件的数据源</param>
        /// <returns></returns>
        public string ExportDataToExcelFile(System.Data.DataTable dataTable)
        {
            string excelfile="";
            try
            {
                excelfile=this.GenerateExcelFile(dataTable,null);
            }
            catch
            {
                throw;
            }
            finally
            {
                GC.Collect();
            }
            return excelfile;
        }

        /// <summary>
        /// 将数据导出到一个Excel文件中，并返回这个Excel文件的路径
        /// </summary>
        /// <param name="dataTable">将要填充到Excel文件的数据源</param>
        /// <param name="FieldTable">用作表头的字段列表，列表的每项是一个哈希表实例，这个哈希表包含有"Field"和"ColumnName"两个键。</param>
        /// <returns></returns>
        public string ExportDataToExcelFile(System.Data.DataTable dataTable,ArrayList FieldTable)
        {
            string excelfile="";
            try
            {
                excelfile=this.GenerateExcelFile(dataTable,FieldTable);
            }
            catch
            {
                throw;
            }
            finally
            {
                GC.Collect();
            }
            return excelfile;
        }
    }
}
